{
"cells": [
{
"cell_type": "code",
"execution_count": 1,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"from sqlalchemy import create_engine\n",
"from sqlalchemy.orm import sessionmaker\n",
"\n",
"engine = create_engine('sqlite:///:memory:')\n",
"\n",
"Session = sessionmaker(bind=engine)\n",
"\n",
"session = Session()"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"from datetime import datetime\n",
"\n",
"from sqlalchemy import Column, Integer, Numeric, String, DateTime, ForeignKey, Boolean\n",
"from sqlalchemy.ext.declarative import declarative_base\n",
"from sqlalchemy.orm import relationship, backref\n",
"\n",
"\n",
"Base = declarative_base()\n",
"\n",
"\n",
"class Cookie(Base):\n",
"    __tablename__ = 'cookies'\n",
"\n",
"    cookie_id = Column(Integer, primary_key=True)\n",
"    cookie_name = Column(String(50), index=True)\n",
"    cookie_recipe_url = Column(String(255))\n",
"    cookie_sku = Column(String(55))\n",
"    quantity = Column(Integer())\n",
"    unit_cost = Column(Numeric(12, 2))\n",
"    \n",
"    def __repr__(self):\n",
"        return \"Cookie(cookie_name='{self.cookie_name}', \" \\\n",
"                       \"cookie_recipe_url='{self.cookie_recipe_url}', \" \\\n",
"                       \"cookie_sku='{self.cookie_sku}', \" \\\n",
"                       \"quantity={self.quantity}, \" \\\n",
"                       \"unit_cost={self.unit_cost})\".format(self=self)\n",
"    \n",
"    \n",
"class User(Base):\n",
"    __tablename__ = 'users'\n",
"    \n",
"    user_id = Column(Integer(), primary_key=True)\n",
"    username = Column(String(15), nullable=False, unique=True)\n",
"    email_address = Column(String(255), nullable=False)\n",
"    phone = Column(String(20), nullable=False)\n",
"    password = Column(String(25), nullable=False)\n",
"    created_on = Column(DateTime(), default=datetime.now)\n",
"    updated_on = Column(DateTime(), default=datetime.now, onupdate=datetime.now)\n",
"    \n",
"    def __repr__(self):\n",
"        return \"User(username='{self.username}', \" \\\n",
"                     \"email_address='{self.email_address}', \" \\\n",
"                     \"phone='{self.phone}', \" \\\n",
"                     \"password='{self.password}')\".format(self=self)\n",
"    \n",
"\n",
"class Order(Base):\n",
"    __tablename__ = 'orders'\n",
"    order_id = Column(Integer(), primary_key=True)\n",
"    user_id = Column(Integer(), ForeignKey('users.user_id'))\n",
"    shipped = Column(Boolean(), default=False)\n",
"    \n",
"    user =  relationship(\"User\", backref=backref('orders', order_by=order_id))\n",
"    \n",
"    def __repr__(self):\n",
"        return \"Order(user_id={self.user_id}, \" \\\n",
"                      \"shipped={self.shipped})\".format(self=self)\n",
"\n",
"\n",
"class LineItem(Base):\n",
"    __tablename__ = 'line_items'\n",
"    line_item_id = Column(Integer(), primary_key=True)\n",
"    order_id = Column(Integer(), ForeignKey('orders.order_id'))\n",
"    cookie_id = Column(Integer(), ForeignKey('cookies.cookie_id'))\n",
"    quantity = Column(Integer())\n",
"    extended_cost = Column(Numeric(12, 2))\n",
"    \n",
"    order = relationship(\"Order\", backref=backref('line_items', order_by=line_item_id))\n",
"    cookie = relationship(\"Cookie\", uselist=False)\n",
"\n",
"    def __repr__(self):\n",
"        return \"LineItems(order_id={self.order_id}, \" \\\n",
"                          \"cookie_id={self.cookie_id}, \" \\\n",
"                          \"quantity={self.quantity}, \" \\\n",
"                          \"extended_cost={self.extended_cost})\".format(\n",
"                    self=self)    \n",
"    \n",
"Base.metadata.create_all(engine)"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"cc_cookie = Cookie(cookie_name='chocolate chip', \n",
"                   cookie_recipe_url='http://some.aweso.me/cookie/recipe.html', \n",
"                   cookie_sku='CC01', \n",
"                   quantity=12, \n",
"                   unit_cost=0.50)"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"session.add(cc_cookie)"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"session.commit()"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
"/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/site-packages/sqlalchemy/sql/sqltypes.py:565: SAWarning: Dialect sqlite+pysqlite does *not* support Decimal objects natively, and SQLAlchemy must convert from floating point - rounding errors and other issues may occur. Please consider storing Decimal numbers as strings or integers on this platform for lossless storage.\n",
"  'storage.' % (dialect.name, dialect.driver))\n"
]
},
{
"data": {
"text/plain": [
"1"
]
},
"execution_count": 6,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"cc_cookie.cookie_id"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"dcc = Cookie(cookie_name='dark chocolate chip',\n",
"             cookie_recipe_url='http://some.aweso.me/cookie/recipe_dark.html',\n",
"             cookie_sku='CC02',\n",
"             quantity=1,\n",
"             unit_cost=0.75)\n",
"mol = Cookie(cookie_name='molasses',\n",
"             cookie_recipe_url='http://some.aweso.me/cookie/recipe_molasses.html',\n",
"             cookie_sku='MOL01',\n",
"             quantity=1,\n",
"             unit_cost=0.80)\n",
"session.add(dcc)\n",
"session.add(mol)\n",
"session.flush()"
]
},
{
"cell_type": "code",
"execution_count": 8,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"2\n",
"3\n"
]
}
],
"source": [
"print(dcc.cookie_id)\n",
"print(mol.cookie_id)"
]
},
{
"cell_type": "code",
"execution_count": 9,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"c1 = Cookie(cookie_name='peanut butter',\n",
"            cookie_recipe_url='http://some.aweso.me/cookie/peanut.html',\n",
"            cookie_sku='PB01',\n",
"            quantity=24,\n",
"            unit_cost=0.25)\n",
"c2 = Cookie(cookie_name='oatmeal raisin',\n",
"            cookie_recipe_url='http://some.okay.me/cookie/raisin.html',\n",
"            cookie_sku='EWW01',\n",
"            quantity=100,\n",
"            unit_cost=1.00)"
]
},
{
"cell_type": "code",
"execution_count": 10,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"session.bulk_save_objects([c1,c2])\n",
"session.commit()"
]
},
{
"cell_type": "code",
"execution_count": 11,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"c1.cookie_id"
]
},
{
"cell_type": "code",
"execution_count": 12,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"[Cookie(cookie_name='chocolate chip', cookie_recipe_url='http://some.aweso.me/cookie/recipe.html', cookie_sku='CC01', quantity=12, unit_cost=0.50), Cookie(cookie_name='dark chocolate chip', cookie_recipe_url='http://some.aweso.me/cookie/recipe_dark.html', cookie_sku='CC02', quantity=1, unit_cost=0.75), Cookie(cookie_name='molasses', cookie_recipe_url='http://some.aweso.me/cookie/recipe_molasses.html', cookie_sku='MOL01', quantity=1, unit_cost=0.80), Cookie(cookie_name='peanut butter', cookie_recipe_url='http://some.aweso.me/cookie/peanut.html', cookie_sku='PB01', quantity=24, unit_cost=0.25), Cookie(cookie_name='oatmeal raisin', cookie_recipe_url='http://some.okay.me/cookie/raisin.html', cookie_sku='EWW01', quantity=100, unit_cost=1.00)]\n"
]
}
],
"source": [
"cookies = session.query(Cookie).all()\n",
"print(cookies)"
]
},
{
"cell_type": "code",
"execution_count": 13,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Cookie(cookie_name='chocolate chip', cookie_recipe_url='http://some.aweso.me/cookie/recipe.html', cookie_sku='CC01', quantity=12, unit_cost=0.50)\n",
"Cookie(cookie_name='dark chocolate chip', cookie_recipe_url='http://some.aweso.me/cookie/recipe_dark.html', cookie_sku='CC02', quantity=1, unit_cost=0.75)\n",
"Cookie(cookie_name='molasses', cookie_recipe_url='http://some.aweso.me/cookie/recipe_molasses.html', cookie_sku='MOL01', quantity=1, unit_cost=0.80)\n",
"Cookie(cookie_name='peanut butter', cookie_recipe_url='http://some.aweso.me/cookie/peanut.html', cookie_sku='PB01', quantity=24, unit_cost=0.25)\n",
"Cookie(cookie_name='oatmeal raisin', cookie_recipe_url='http://some.okay.me/cookie/raisin.html', cookie_sku='EWW01', quantity=100, unit_cost=1.00)\n"
]
}
],
"source": [
"for cookie in session.query(Cookie):  \n",
"    print(cookie)"
]
},
{
"cell_type": "code",
"execution_count": 14,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"(u'chocolate chip', 12)\n"
]
}
],
"source": [
"print(session.query(Cookie.cookie_name, Cookie.quantity).first())"
]
},
{
"cell_type": "code",
"execution_count": 15,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"  1 - dark chocolate chip\n",
"  1 - molasses\n",
" 12 - chocolate chip\n",
" 24 - peanut butter\n",
"100 - oatmeal raisin\n"
]
}
],
"source": [
"for cookie in session.query(Cookie).order_by(Cookie.quantity):\n",
"    print('{:3} - {}'.format(cookie.quantity, cookie.cookie_name))"
]
},
{
"cell_type": "code",
"execution_count": 16,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"100 - oatmeal raisin\n",
" 24 - peanut butter\n",
" 12 - chocolate chip\n",
"  1 - dark chocolate chip\n",
"  1 - molasses\n"
]
}
],
"source": [
"from sqlalchemy import desc\n",
"for cookie in session.query(Cookie).order_by(desc(Cookie.quantity)):\n",
"    print('{:3} - {}'.format(cookie.quantity, cookie.cookie_name))"
]
},
{
"cell_type": "code",
"execution_count": 17,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"[u'dark chocolate chip', u'molasses']\n"
]
}
],
"source": [
"query = session.query(Cookie).order_by(Cookie.quantity)[:2]\n",
"print([result.cookie_name for result in query])"
]
},
{
"cell_type": "code",
"execution_count": 18,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"[u'dark chocolate chip', u'molasses']\n"
]
}
],
"source": [
"query = session.query(Cookie).order_by(Cookie.quantity).limit(2)\n",
"print([result.cookie_name for result in query])"
]
},
{
"cell_type": "code",
"execution_count": 19,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"138\n"
]
}
],
"source": [
"from sqlalchemy import func\n",
"inv_count = session.query(func.sum(Cookie.quantity)).scalar()\n",
"print(inv_count)"
]
},
{
"cell_type": "code",
"execution_count": 20,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"(5,)\n"
]
}
],
"source": [
"rec_count = session.query(func.count(Cookie.cookie_name)).first()\n",
"print(rec_count)"
]
},
{
"cell_type": "code",
"execution_count": 21,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"['inventory_count']\n",
"5\n"
]
}
],
"source": [
"rec_count = session.query(func.count(Cookie.cookie_name) \\\n",
"                          .label('inventory_count')).first()\n",
"print(rec_count.keys())\n",
"print(rec_count.inventory_count)"
]
},
{
"cell_type": "code",
"execution_count": 22,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Cookie(cookie_name='chocolate chip', cookie_recipe_url='http://some.aweso.me/cookie/recipe.html', cookie_sku='CC01', quantity=12, unit_cost=0.50)\n"
]
}
],
"source": [
"record = session.query(Cookie).filter(Cookie.cookie_name == 'chocolate chip').first()\n",
"print(record)"
]
},
{
"cell_type": "code",
"execution_count": 23,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Cookie(cookie_name='chocolate chip', cookie_recipe_url='http://some.aweso.me/cookie/recipe.html', cookie_sku='CC01', quantity=12, unit_cost=0.50)\n"
]
}
],
"source": [
"record = session.query(Cookie).filter_by(cookie_name='chocolate chip').first()\n",
"print(record)"
]
},
{
"cell_type": "code",
"execution_count": 24,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"chocolate chip\n",
"dark chocolate chip\n"
]
}
],
"source": [
"query = session.query(Cookie).filter(Cookie.cookie_name.like('%chocolate%'))\n",
"for record in query:                       \n",
"    print(record.cookie_name)"
]
},
{
"cell_type": "code",
"execution_count": 25,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"(u'chocolate chip', u'SKU-CC01')\n",
"(u'dark chocolate chip', u'SKU-CC02')\n",
"(u'molasses', u'SKU-MOL01')\n",
"(u'peanut butter', u'SKU-PB01')\n",
"(u'oatmeal raisin', u'SKU-EWW01')\n"
]
}
],
"source": [
"results = session.query(Cookie.cookie_name, 'SKU-' + Cookie.cookie_sku).all()\n",
"for row in results:\n",
"    print(row)"
]
},
{
"cell_type": "code",
"execution_count": 26,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"chocolate chip - 6.00\n",
"dark chocolate chip - 0.75\n",
"molasses - 0.80\n",
"peanut butter - 6.00\n",
"oatmeal raisin - 100.00\n"
]
}
],
"source": [
"from sqlalchemy import cast\n",
"query = session.query(Cookie.cookie_name,\n",
"                      cast((Cookie.quantity * Cookie.unit_cost), \n",
"                           Numeric(12,2)).label('inv_cost'))\n",
"for result in query:\n",
"    print('{} - {}'.format(result.cookie_name, result.inv_cost))"
]
},
{
"cell_type": "code",
"execution_count": 27,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"peanut butter\n"
]
}
],
"source": [
"from sqlalchemy import and_, or_, not_\n",
"query = session.query(Cookie).filter(\n",
"    Cookie.quantity > 23,\n",
"    Cookie.unit_cost < 0.40\n",
")\n",
"for result in query:\n",
"    print(result.cookie_name)"
]
},
{
"cell_type": "code",
"execution_count": 28,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"chocolate chip\n",
"dark chocolate chip\n",
"peanut butter\n"
]
}
],
"source": [
"from sqlalchemy import and_, or_, not_\n",
"query = session.query(Cookie).filter(\n",
"    or_(\n",
"        Cookie.quantity.between(10, 50),\n",
"        Cookie.cookie_name.contains('chip')\n",
"    )\n",
")\n",
"for result in query:\n",
"    print(result.cookie_name)"
]
},
{
"cell_type": "code",
"execution_count": 29,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"132\n"
]
}
],
"source": [
"query = session.query(Cookie)\n",
"cc_cookie = query.filter(Cookie.cookie_name == \"chocolate chip\").first()\n",
"cc_cookie.quantity = cc_cookie.quantity + 120\n",
"session.commit()\n",
"print(cc_cookie.quantity)"
]
},
{
"cell_type": "code",
"execution_count": 30,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"112\n"
]
}
],
"source": [
"query = session.query(Cookie)\n",
"query = query.filter(Cookie.cookie_name == \"chocolate chip\")\n",
"query.update({Cookie.quantity: Cookie.quantity - 20})\n",
"\n",
"cc_cookie = query.first()\n",
"print(cc_cookie.quantity)"
]
},
{
"cell_type": "code",
"execution_count": 31,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"None\n"
]
}
],
"source": [
"query = session.query(Cookie)\n",
"query = query.filter(Cookie.cookie_name == \"dark chocolate chip\")\n",
"dcc_cookie = query.one()\n",
"session.delete(dcc_cookie)\n",
"session.commit()\n",
"dcc_cookie = query.first()\n",
"print(dcc_cookie)"
]
},
{
"cell_type": "code",
"execution_count": 32,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"None\n"
]
}
],
"source": [
"query = session.query(Cookie)\n",
"query = query.filter(Cookie.cookie_name == \"molasses\")\n",
"query.delete()\n",
"mol_cookie = query.first()\n",
"print(mol_cookie)"
]
},
{
"cell_type": "code",
"execution_count": 33,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"cookiemon = User(username='cookiemon', \n",
"                 email_address='mon@cookie.com', \n",
"                 phone='111-111-1111', \n",
"                 password='password')\n",
"cakeeater = User(username='cakeeater', \n",
"                 email_address='cakeeater@cake.com', \n",
"                 phone='222-222-2222', \n",
"                 password='password')\n",
"pieperson = User(username='pieperson', \n",
"                 email_address='person@pie.com', \n",
"                 phone='333-333-3333', \n",
"                 password='password')\n",
"session.add(cookiemon)\n",
"session.add(cakeeater)\n",
"session.add(pieperson)\n",
"session.commit()"
]
},
{
"cell_type": "code",
"execution_count": 34,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"o1 = Order()\n",
"o1.user = cookiemon\n",
"session.add(o1)\n",
"\n",
"cc = session.query(Cookie).filter(Cookie.cookie_name == \n",
"                                  \"chocolate chip\").one()\n",
"line1 = LineItem(cookie=cc, quantity=2, extended_cost=1.00)\n",
"\n",
"pb = session.query(Cookie).filter(Cookie.cookie_name == \n",
"                                  \"peanut butter\").one()\n",
"line2 = LineItem(quantity=12, extended_cost=3.00)\n",
"line2.cookie = pb \n",
"line2.order = o1\n",
"\n",
"o1.line_items.append(line1)\n",
"o1.line_items.append(line2)\n",
"session.commit()"
]
},
{
"cell_type": "code",
"execution_count": 35,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"o2 = Order()\n",
"o2.user = cakeeater\n",
"\n",
"cc = session.query(Cookie).filter(Cookie.cookie_name == \n",
"                                  \"chocolate chip\").one()\n",
"line1 = LineItem(cookie=cc, quantity=24, extended_cost=12.00)\n",
"\n",
"oat = session.query(Cookie).filter(Cookie.cookie_name == \n",
"                                   \"oatmeal raisin\").one()\n",
"line2 = LineItem(cookie=oat, quantity=6, extended_cost=6.00)\n",
"\n",
"o2.line_items.append(line1)\n",
"o2.line_items.append(line2)\n",
"\n",
"session.add(o2)\n",
"session.commit()"
]
},
{
"cell_type": "code",
"execution_count": 36,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"[(1, u'cookiemon', u'111-111-1111', u'peanut butter', 12, Decimal('3.00')), (1, u'cookiemon', u'111-111-1111', u'chocolate chip', 2, Decimal('1.00'))]\n"
]
}
],
"source": [
"query = session.query(Order.order_id, User.username, User.phone,\n",
"                      Cookie.cookie_name, LineItem.quantity,\n",
"                      LineItem.extended_cost)\n",
"query = query.join(User).join(LineItem).join(Cookie)\n",
"results = query.filter(User.username == 'cookiemon').all()\n",
"print(results)"
]
},
{
"cell_type": "code",
"execution_count": 37,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"(u'cakeeater', 1)\n",
"(u'cookiemon', 1)\n",
"(u'pieperson', 0)\n"
]
}
],
"source": [
"query = session.query(User.username, func.count(Order.order_id))\n",
"query = query.outerjoin(Order).group_by(User.username)\n",
"for row in query:\n",
"    print(row)"
]
},
{
"cell_type": "code",
"execution_count": 38,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"class Employee(Base):\n",
"    __tablename__ = 'employees'\n",
"    \n",
"    id = Column(Integer(), primary_key=True)\n",
"    manager_id = Column(Integer(), ForeignKey('employees.id'))\n",
"    name = Column(String(255), nullable=False)\n",
"    \n",
"    manager = relationship(\"Employee\", backref=backref('reports'), remote_side=[id])\n",
"\n",
"Base.metadata.create_all(engine)"
]
},
{
"cell_type": "code",
"execution_count": 39,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"marsha = Employee(name='Marsha')\n",
"fred = Employee(name='Fred')\n",
"marsha.reports.append(fred)\n",
"session.add(marsha)\n",
"session.commit()"
]
},
{
"cell_type": "code",
"execution_count": 40,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Fred\n"
]
}
],
"source": [
"for report in marsha.reports:\n",
"    print(report.name)"
]
},
{
"cell_type": "code",
"execution_count": 41,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"(u'cakeeater', 1)\n",
"(u'cookiemon', 1)\n",
"(u'pieperson', 0)\n"
]
}
],
"source": [
"query = session.query(User.username, func.count(Order.order_id))\n",
"query = query.outerjoin(Order).group_by(User.username)\n",
"for row in query:\n",
"    print(row)"
]
},
{
"cell_type": "code",
"execution_count": 42,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/plain": [
"[(2, u'cakeeater', u'222-222-2222', u'chocolate chip', 24, Decimal('12.00')),\n",
" (2, u'cakeeater', u'222-222-2222', u'oatmeal raisin', 6, Decimal('6.00'))]"
]
},
"execution_count": 42,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"def get_orders_by_customer(cust_name):\n",
"    query = session.query(Order.order_id, User.username, User.phone,\n",
"                      Cookie.cookie_name, LineItem.quantity,\n",
"                      LineItem.extended_cost)\n",
"    query = query.join(User).join(LineItem).join(Cookie)\n",
"    results = query.filter(User.username == cust_name).all()\n",
"    return results\n",
"\n",
"get_orders_by_customer('cakeeater')"
]
},
{
"cell_type": "code",
"execution_count": 43,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"[(2, u'cakeeater', u'222-222-2222')]\n",
"[(2, u'cakeeater', u'222-222-2222', u'chocolate chip', 24, Decimal('12.00')), (2, u'cakeeater', u'222-222-2222', u'oatmeal raisin', 6, Decimal('6.00'))]\n",
"[]\n",
"[(2, u'cakeeater', u'222-222-2222')]\n",
"[(2, u'cakeeater', u'222-222-2222', u'chocolate chip', 24, Decimal('12.00')), (2, u'cakeeater', u'222-222-2222', u'oatmeal raisin', 6, Decimal('6.00'))]\n"
]
}
],
"source": [
"def get_orders_by_customer(cust_name, shipped=None, details=False):\n",
"    query = session.query(Order.order_id, User.username, User.phone)\n",
"    query = query.join(User)\n",
"    if details:\n",
"        query = query.add_columns(Cookie.cookie_name, LineItem.quantity,\n",
"                          LineItem.extended_cost)\n",
"        query = query.join(LineItem).join(Cookie)\n",
"    if shipped is not None:\n",
"        query = query.filter(Order.shipped == shipped)\n",
"    results = query.filter(User.username == cust_name).all()\n",
"    return results\n",
"\n",
"print(get_orders_by_customer('cakeeater'))\n",
"\n",
"print(get_orders_by_customer('cakeeater', details=True))\n",
"\n",
"print(get_orders_by_customer('cakeeater', shipped=True))\n",
"\n",
"print(get_orders_by_customer('cakeeater', shipped=False))\n",
"\n",
"print(get_orders_by_customer('cakeeater', shipped=False, details=True))"
]
},
{
"cell_type": "code",
"execution_count": 44,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"[User(username='cookiemon', email_address='mon@cookie.com', phone='111-111-1111', password='password')]\n"
]
}
],
"source": [
"from sqlalchemy import text\n",
"query = session.query(User).filter(text(\"username='cookiemon'\"))\n",
"print(query.all())"
]
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 2",
"language": "python",
"name": "python2"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 2
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython2",
"version": "2.7.10"
}
},
"nbformat": 4,
"nbformat_minor": 0
}